---
title: MySQL
description: MySQL
date: 2022-06-14 10:30:00
image: /img/mysql.svg
---
import Alert from '@/components/post/alert.astro'

[[toc]]

## 注意事项

**SQL关键字总是大写，以示突出，表名和列名均使用小写**

> 通常情况下，字段应该避免允许为NULL。**不允许为NULL可以简化查询条件，加快查询速度**，也利于应用程序读取数据后无需判断是否为NULL

如果你不知道应该采用哪种引擎，记住总是选择 **InnoDB(插入效率高，且支持事务)** 就好了

## 概念

### 主键

**唯一区分出不同记录的字段**

<Alert type="warning">**不使用任何业务相关的字段作为主键**</Alert>

<Alert type="info">如果使用INT自增类型，那么当一张表的记录数超过 2147483647（约21亿）时，会达到上限而出错。**使用BIGINT自增类型则可以最多约922亿亿条记录。**</Alert>

**联合主键：**
- **允许其中一列存在重复，只要不是所有的主键列重复都可以被接受**
- 没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升

### 外键

在一对多的关系中，在**多**的表中增加对应的列，列的**取值范围为所有 `一` 的主键的值**

```sql
-- 对 students 表执行操作
ALTER TABLE students
-- 添加新的约束关系，约束名称随意
ADD CONSTRAINT fk_class_id
-- 将 students 表中的 class_id 列定义为外键
FOREIGN KEY (`class_id`)
-- 关联到 classes 的 id 列
REFERENCES classes (`id`);
```

> 由于**外键约束会降低数据库的性能**，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，`class_id` 仅仅是一个普通的列，只是它起到了外键的作用而已。

```sql
-- 对 students 表执行操作
ALTER TABLE students
-- 删除之前定义的外键约束关系，不会影响对应的列
DROP FOREIGN KEY fk_class_id;
```

### 索引

**对某一列或多列进行 `预排序` 的数据结构，通常用于优化海量数据的查询速度**

**索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高**

> 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一

```sql
ALTER TABLE students
-- 添加唯一索引，id_card 字段不得重复
ADD UNIQUE INDEX uni_id_card (`id_card`);
-- 添加唯一约束但不创建唯一索引，name 也不得重复
ADD CONSTRAINT uni_name UNIQUE (`name`);
```

数据库索引对于用户和应用程序来说都是透明的，不添加索引也只会是查询速度稍慢而已

## 基础操作

### 命令行

```bash
# 使用密码登录 root 用户
mysql -u root -p
# 连接远程数据库
mysql -h 10.0.1.99 -u root -p

# 列出所有数据库
SHOW DATABASES;
# 切换到某个数据库
USE database_name;
# 展示此数据库的所有表
SHOW TABLES;
# 查看表结构
DESC table_name;
# 查看建表语句
SHOW CREATE TABLE table_name;
# 退出
EXIT
```

### 初始化(建库，建表，插入数据)

```sql
-- 如果test数据库不存在，就创建test数据库：
CREATE DATABASE IF NOT EXISTS test;
-- 删库
-- DROP DATABASE test;

-- 切换到test数据库
USE test;

-- 删除classes表和students表（如果存在）：
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS students;

-- 创建classes表：
CREATE TABLE classes (
  -- 非空自增 id
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  -- 姓名
  `name` VARCHAR(100) NOT NULL,
  -- 设置 id 为主键
  PRIMARY KEY (`id`)
  -- 引擎 InnoDB 字符集 utf-8
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建students表：
CREATE TABLE students (
  -- 非空自增 id
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  -- 非空班级 id
  `class_id` BIGINT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `gender` VARCHAR(1) NOT NULL,
  `score` INT NOT NULL,
  -- 设置 id 为主键
  PRIMARY KEY (id),
  -- 设置 class_id 的外键约束
  CONSTRAINT `fk_class_id` FOREIGN KEY (`class_id`) REFERENCES classes (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入classes记录：
INSERT INTO classes(`id`, `name`) VALUES (1, '一班');
INSERT INTO classes(`id`, `name`) VALUES (2, '二班');
INSERT INTO classes(`id`, `name`) VALUES (3, '三班');
INSERT INTO classes(`id`, `name`) VALUES (4, '四班');

-- 插入students记录：
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (1, 1, '小明', 'M', 90);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (2, 1, '小红', 'F', 95);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (3, 1, '小军', 'M', 88);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (4, 1, '小米', 'F', 73);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (5, 2, '小白', 'F', 81);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (6, 2, '小兵', 'M', 55);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (7, 2, '小林', 'M', 85);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (8, 3, '小新', 'F', 91);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (9, 3, '小王', 'M', 89);
INSERT INTO students (`id`, `class_id`, `name`, `gender`, `score`) VALUES (10, 3, '小丽', 'F', 85);

-- OK:
SELECT 'ok' as 'result:';
```

### 对表进行修改操作

```sql
-- 新增一列
ALTER TABLE `students`
ADD COLUMN `birth` VARCHAR(10) NOT NULL;
-- 修改一列
ALTER TABLE `students`
CHANGE COLUMN `birth` `birthday` VARCHAR(20) NOT NULL;
-- 删除列
ALTER TABLE `students` DROP COLUMN `birthday`;
```

## 查询

```sql
-- 查询某个表中的全部数据
SELECT * FROM `table_name`
-- 不带 FROM 子语句，主要用来测试数据库连接
SELECT 1; -- 1
SELECT 100 + 200; -- 300
SELECT 'ok' as 'result:'; -- result: ok

-- 等价操作 [60, 90]
WHERE score >= 60 AND score <= 90;
WHERE score BETWEEN 60 AND 90;

-- 等价操作
WHERE score >= 60 OR score <= 90;
WHERE 60 <= score <= 90;

-- IN 60 || 90
WHERE score IN (60, 90);
```

### 条件查询

```sql
-- 单条件
SELECT * FROM `students` WHERE `score` >= 80;
-- 与
SELECT * FROM `students` WHERE `score` >= 80 AND `gender` = 'M';
-- 或
SELECT * FROM `students` WHERE `score` >= 80 OR `gender` = 'M';
-- 非，等价于 WHERE class_id <> 2;
SELECT * FROM `students` WHERE NOT `class_id` = 2;
-- 多条件
--    优先级 括号 > NOT > AND > OR
SELECT * FROM `students`
WHERE (`score` < 80 OR `score` > 90)
AND `gender` = 'M';
-- 选取指定的列，并将 score 映射(别名)为 points
SELECT `id`, `score` `points`, `name` FROM `students`
WHERE `gender` = 'M';
```

### 排序

```sql
-- 排序(排序字段必须指出，不要使用* ！！！)
-- 按分数升序(默认)排序 ASC
SELECT `id`, `name`, `gender`, `score`
FROM `students`
ORDER BY `score`;
-- 按分数降序排序
SELECT `id`, `name`, `gender`, `score`
FROM `students`
ORDER BY `score` DESC;
-- 按分数降序排序，分数相同然后再按性别升序
SELECT `id`, `name`, `gender`, `score`
FROM `students`
ORDER BY `score` DESC, `gender`;
-- 拥有条件语句时，排序语句跟在条件语句后面
SELECT `id`, `name`, `gender`, `score`
FROM `students`
WHERE `class_id` = 1
ORDER BY `score` DESC;
```

### 分页

```sql
-- LIMIT 每页条数
-- OFFSET 跳过前面的多少条，默认为 0
-- 每页 3 条数据，取第 1 页
SELECT `id`, `name`, `gender`, `score`
FROM `students`
ORDER BY `score` DESC
LIMIT 3 OFFSET 0;
-- 每页 3 条数据，取第 2 页
SELECT `id`, `name`, `gender`, `score`
FROM `students`
ORDER BY `score` DESC
LIMIT 3 OFFSET 3;
-- 每页 3 条数据，取第 3 页
SELECT `id`, `name`, `gender`, `score`
FROM `students`
ORDER BY `score` DESC
LIMIT 3 OFFSET 6;
```

### 聚合查询

**内建函数**

<Alert type="warning">**如果聚合查询的 `WHERE` 条件没有匹配到任何行，`COUNT()` 会返回 0，而 `SUM()`、`AVG()`、`MAX()` 和 `MIN()` 会返回 `NULL`**</Alert>

```sql
-- 直接返回统计的数值 ===> COUNT(id)
SELECT COUNT(*) FROM `students`;
-- 将统计结果返回为一行一列的二维表
SELECT COUNT(*) `num` FROM `students`;
-- 聚合条件查询
-- 统计男生的数量
SELECT COUNT(*) `boys`
FROM `students`
WHERE `gender` = 'M';
-- 计算男生的平均成绩
SELECT AVG(`score`) `average`
FROM `students`
WHERE `gender` = 'M';
```

**分组**

<Alert type="error">**结果列只能是分组列的子集**</Alert>

```sql
-- 分别统计每个班的人数
SELECT COUNT(*) `num`
FROM `students`
GROUP BY `class_id`;
-- 返回每个班级 ID 各自对应的人数
SELECT `class_id`, COUNT(*) `num`
FROM `students`
GROUP BY `class_id`;
-- name 不是分组字段，会报错
SELECT `name`, `class_id`, COUNT(*) `num`
FROM `students`
GROUP BY `class_id`;
-- 分别统计各班男女生各自的人数
SELECT `class_id`, `gender`, COUNT(*) `num`
FROM `students`
GROUP BY `class_id`, `gender`;
-- 分别统计各班男女生各自的平均分
SELECT `class_id`, `gender`, AVG(`score`) `class_avg`
FROM `students`
GROUP BY `class_id`, `gender`;
```

### 多表联合查询

```sql
-- 返回两张表的笛卡尔积，共 m * n 条数据
SELECT * FROM `students`, `classes`;
-- 表别名 + 列别名 + 条件查询
SELECT
  `s`.id `sid`,
  `s`.name,
  `s`.gender,
  `s`.score,
  `c`.id `cid`,
  `c`.name `cname`
FROM `students` `s`, `classes` `c`
WHERE `s`.gender = 'M' AND `c`.id = 1;
```

**INNER JOIN**

<Alert type="info">**只返回同时存在于两张表的行数据(A&B)**</Alert>

```sql
SELECT
  `s`.id,
  `s`.name,
  `c`.name class_name,
  `s`.gender,
  `s`.score
-- 主表
FROM `students` `s`
-- 需要连接的表
INNER JOIN `classes` `c`
-- 连接条件
ON `s`.class_id = `c`.id
-- 查询条件
WHERE `s`.gender = 'M'
-- 排序条件
ORDER BY score DESC;
```

**OUTER JOIN**

<Alert type="info">`RIGHT OUTER JOIN` **返回右表都存在的记录(A&B+B)**。如果某一行仅在右表存在，那么结果集就会以 `NULL` 填充剩下的字段</Alert>

<Alert type="info">`LEFT OUTER JOIN` **返回左表都存在的记录(A&B+A)**</Alert>

<Alert type="info">`FULL OUTER JOIN` **返回两张表所有的记录(A+B)，并且将对方不存在的列填充为 `NULL`**</Alert>


```sql
SELECT
  `s`.id,
  `s`.name,
  `s`.class_id,
  `c`.name class_name,
  `s`.gender,
  `s`.score
FROM `students` `s`
RIGHT OUTER JOIN `classes` `c`
ON `s`.class_id = `c`.id;
```

## 插入

```sql
-- 往 students 表中插入一条数据
INSERT INTO
`students` (`class_id`, `name`, `gender`, `score`)
VALUES (2, '大牛', 'M', 80);
-- 一次插入多条数据
INSERT INTO
`students` (`class_id`, `name`, `gender`, `score`)
VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);
```

## 修改

<Alert type="info">**即使未匹配到任何数据，也不会报错**</Alert>

```sql
-- 修改 id ∈ [5， 7] 的学生的名字和分数
UPDATE `students`
SET
  `name` = '小牛',
  `score` = 77
WHERE `id` >= 5 AND `id` <= 7;
-- 80 分以下的学生每人加十分
UPDATE `students`
SET `score` = `score` + 10
WHERE `score` < 80;
-- 不加条件，等同于修改所有学生的分数
UPDATE `students` SET `score` = 60;
```


## 删除

<Alert type="info">**即使未匹配到任何数据，也不会报错**</Alert>

```sql
-- 删除 id ∈ [5， 7] 的学生
DELETE FROM `students` WHERE `id` >= 5 AND `id` <= 7;
-- 不加条件，等同于全部删除
DELETE FROM `students`;
```

## 实用语句

```sql
-- 不存在则插入，存在则替换
REPLACE INTO
`students` (`id`, `class_id`, `name`, `gender`, `score`)
VALUES (1, 1, '小明', 'F', 99);

-- 不存在则插入，存在则更新部分数据
INSERT INTO
`students` (`id`, `class_id`, `name`, `gender`, `score`)
VALUES (1, 1, '小明', 'F', 99)
ON DUPLICATE KEY
UPDATE
  `name` = '小明',
  `gender` = 'F',
  `score` = 99
;

-- 不存在则插入，存在则不作任何操作
INSERT IGNORE INTO
`students` (`id`, `class_id`, `name`, `gender`, `score`)
VALUES (1, 1, '小明', 'F', 99);

--- 快照 ---
-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE `students_of_class1`
SELECT * FROM `students`
WHERE `class_id` = 1;

--- 写入结果集 ---
CREATE TABLE `statistics` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `class_id` BIGINT NOT NULL,
  `average` DOUBLE NOT NULL,
  PRIMARY KEY (`id`)
);
-- 直接将各班的平均成绩写入新创建的表中
INSERT INTO `statistics` (`class_id`, `average`)
SELECT `class_id`, AVG(`score`)
FROM `students`
GROUP BY `class_id`;

-- 强制实用指定的索引
SELECT * FROM `students`
-- 必须保证此索引存在
FORCE INDEX (`idx_class_id`)
WHERE `class_id` = 1
ORDER BY `id` DESC;
```

## 事务

**一批 SQL 语句必须全部执行成功才能真正的成功，否则需要回滚**

对于**单条** `SQL` 语句，数据库系统自动将其作为一个事务执行，这种事务被称为**隐式事务**

```sql
-- 显示事务
-- 声明事务开始
BEGIN;

-- xxx 一次事务中需要执行的 SQL 语句
-- xxx 一次事务中需要执行的 SQL 语句
-- xxx 一次事务中需要执行的 SQL 语句
-- ...

-- 声明事务结束，进行提交，提交失败会自动回滚
COMMIT;
-- 主动回滚整个事务
ROLLBACK;
```